AlchemyJ by Axisoft

5.0

ajFIXJSONToMsg function

Available since AlchemyJ v5.0

Description

The ajFIXJsonToMsg function can convert JSON string to FIX message. The JSON data format should be contain FIX message standard Header, Body and Trailer node as below sample string.

{
    "Header": {
        "BeginString": "FIX.5.0SP2",
        "SenderCompID": "TRADETECH",
        "SendingTime": "20240730-15:55:12.456",
        "TargetCompID": "COMPASS",
        "MsgType": "8",
        "MsgSeqNum": "1",
        "BodyLength": "168"
    },
    "Body": {
        "Account": "ACC001",
        "CumQty": "0",
        "ExecID": "20240730155512-001",
        "OrderQty": "1000",
        "SecurityIDSource": "8",
        "OrderID": "20240731-001",
        "Side": "1",
        "AvgPx": "0",
        "OrdStatus": "0",
        "Currency": "USD",
        "SecurityID": "AAPL",
        "ExecType": "0",
        "LeavesQty": "1000"
    },
    "Trailer": {
        "CheckSum": "219"
    }
}

Syntax

ajFIXJSONToMsg(json_data,[tag_as_key],[version],[run_condition],[run_by_function_point_only])

Argument NameArgument TypeDescription
json_data
(required)		RangeSpecify content of the JSON from a range of cells. The JSON can span multiple rows and columns, and the content in cells are concatenated into one string from left to right and top to bottom.
tag_as_key
(optional)		BooleanTRUE means the JSON key is FIX message tag number, such as "35". FALSE means the JSON key is FIX message tag name, such as "MsgType".
The default value is FALSE.
version
(optional)		StringSupport version: FIX.4.2, FIX.4.3, FIX.4.4, FIX.5.0, FIX.5.0SP1, FIX.5.0SP2.
The default value is FIX.5.0SP2.
run_condition
(optional)		BooleanThe function will run when the value is TRUE. Otherwise will not run.
The default value is TRUE.
run_by_function_point_only
(optional)		BooleanIf it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point.

If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only.


The default value is TRUE.

The function will return:

1) Return Value: FIX message string.

2) Return Type: Single Value / Multiple values (array formula).

Example

Make sure the AlchemyJ Function Proxy was started up when executing ajFIXJsonToMsg in the AlchemyJ workbook. You can start the proxy from More Tools - Run AlchemyJ Function Proxy.

Example 1

The JSON key is FIX message tag name, tag_as_key parameter value is FALSE.

=ajFIXJSONToMsg(B1,B2,B3,B4,B5)

Use below JSON string convert to FIX message:

{"Header":{"BeginString":"FIX.5.0SP2","SenderCompID":"TRADETECH","SendingTime":"20240730-15:55:12.456","TargetCompID":"COMPASS","MsgType":"8","MsgSeqNum":"1","BodyLength":"168"},"Body":{"Account":"ACC001","CumQty":"0","ExecID":"20240730155512-001","OrderQty":"1000","SecurityIDSource":"8","OrderID":"20240731-001","Side":"1","AvgPx":"0","OrdStatus":"0","Currency":"USD","SecurityID":"AAPL","ExecType":"0","LeavesQty":"1000"},"Trailer":{"CheckSum":"219"}}

Example 2

The JSON key is FIX message tag number, tag_as_key parameter value is TRUE.

=ajFIXJSONToMsg(B1,B2,B3,B4,B5)

Use below JSON string convert to FIX message:

{"Header":{"34":"1","56":"TargetCompID","35":"D","49":"SenderCompID","8":"FIX.5.0SP2","9":"153","52":"20241021-12:00:00.000"},"Body":{"11":"123456","78":[{"661":"1","79":"10"},{"661":"2","79":"20"}],"38":"100","60":"20241021-12:00:00.001","40":"2","21":"1","54":"1"},"Trailer":{"10":"033"}}

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
JSON string is empty.
FIX message version is not support.
JSON string contained field is not belong to this MsgType or FIX message version.
JSON string value type is not FIX message support.
JSON string fields are FIX message tag number, but formula input tag_as_key parameter is FALSE.
JSON string fields are FIX message tag name, but formula input tag_as_key parameter is TRUE.
← ajFIXCheckSumajFIXMsgToJSON →